package com.infodms.dms.dao.conservation;

import java.sql.ResultSet;
import java.util.List;
import java.util.Map;

import com.infodms.dms.common.Constant;
import com.infodms.dms.common.Utility;
import com.infodms.dms.dao.common.BaseDao;
import com.infodms.dms.util.businessUtil.GetVinUtil;
import com.infoservice.po3.bean.PO;
import com.infoservice.po3.bean.PageResult;

public class EnergyConservationOemDao extends BaseDao{
	
	private static final EnergyConservationOemDao dao = new EnergyConservationOemDao();
	
	public static final EnergyConservationOemDao getInstance() {
		return dao;
	}
	/**
	 * 节能惠民申请查询
	 * @param dealerId
	 * @param curPage
	 * @param pageSize
	 * @return
	 */
	
	public PageResult<Map<String, Object>> energyConApplyQuery(
			String saleStartDate,
			String saleEndDate,String reportStartDate,String reportEndDate,
			String dealerCode,String model,String orgId,String areaId,
			String energyNo,String vin,String dPro,String dCity,
			int curPage, int pageSize) {		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT A.CONSERVATION_ID,\n");
		sql.append("       A.CONSERVATION_NO,\n");
		sql.append("       A.DEALER_ID, (SELECT B.DEALER_CODE\n");
		sql.append("                      FROM TM_DEALER B\n");
		sql.append("                     WHERE B.DEALER_ID = A.DEALER_ID) AS DEALERCODE,\n");
		sql.append("       (SELECT C.DEALER_NAME\n");
		sql.append("          FROM TM_DEALER C\n");
		sql.append("         WHERE C.DEALER_ID = A.DEALER_ID) AS DEALERNAME,\n");
		sql.append("       (SELECT D.GROUP_CODE\n");
		sql.append("          FROM TM_VHCL_MATERIAL_GROUP D\n");
		sql.append("         WHERE D.GROUP_ID = A.MODEL_ID) AS MODELCODE,\n");
		sql.append("       (SELECT E.GROUP_NAME\n");
		sql.append("          FROM TM_VHCL_MATERIAL_GROUP E\n");
		sql.append("         WHERE E.GROUP_ID = A.MODEL_ID) AS MODELNAME,\n");
		sql.append("       A.SALES_ID,\n");
		sql.append("       (SELECT G.VIN FROM TM_VEHICLE G WHERE G.VEHICLE_ID=A.VEHICLE_ID) AS VIN,\n");
		sql.append("       A.VEHICLE_ID,\n");
		sql.append("       A.MODEL_ID,\n");
		sql.append("       TO_CHAR(A.SALES_DATE, 'YYYY-MM-dd') AS SALES_DATE,\n");
		sql.append("       A.INVOCE_NO,\n");
		sql.append("       A.VEHICLE_NO,\n");
		sql.append("       A.FACTORY_PRICE,\n");
		sql.append("       A.SALES_PRICE,\n");
		sql.append("       A.DLR_LINKMAN,\n");
		sql.append("       A.DLR_LINKTEL,\n");
		sql.append("       A.DLR_ZIP_CODE,\n");
		sql.append("       A.DLR_PROVICE_ID,\n");
		sql.append("       A.DLR_ADDRESS,\n");
		sql.append("       (SELECT F.CTM_NAME FROM TT_CUSTOMER F WHERE F.CTM_ID=A.CTM_ID) AS CTM_NAME,\n");
		sql.append("       A.CTM_ID,\n");
		sql.append("       A.CTM_LINKMAN,\n");
		sql.append("       A.CTM_LINKTEL,\n");
		sql.append("       A.CTM_ZIP_CODE,\n");
		sql.append("       A.CTM_PROVICE_ID,\n");
		sql.append("       A.CTM_ADDRESS,\n");
		sql.append("       A.ORG_ID,\n");
		sql.append("       A.CONSERVATION_STATUS,\n");
		sql.append("       A.STATUS,\n");
		sql.append("       A.IS_EXPORT,\n");
		sql.append("       A.EXPORT_FLAG,\n");
		sql.append("       A.EXPORT_USER,\n");
		sql.append("       A.EXPORT_POSE,\n");
		sql.append("       A.CREATE_BY,\n");
		sql.append("       TO_CHAR(A.CREATE_DATE, 'YYYY-MM-dd') AS CREATE_DATE,\n");
		sql.append("       A.REMARK\n");
		sql.append("  FROM tt_vs_energy_conservation A\n");
		sql.append(" WHERE 1=1 AND A.CONSERVATION_STATUS="+Constant.VEHICLE_ENERGY_CON_APPLY+"\n");

		if(Utility.testString(model)){
			String[] materialCodes = model.split(",");
			if (null != materialCodes && materialCodes.length > 0) {
				StringBuffer buffer = new StringBuffer();
				for (int i = 0; i < materialCodes.length; i++) {
					buffer.append("'").append(materialCodes[i]).append("'").append(",");
				}
				buffer = buffer.deleteCharAt(buffer.length() - 1);
				sql.append(" AND EXISTS (SELECT K.GROUP_ID FROM TM_VHCL_MATERIAL_GROUP K WHERE K.GROUP_ID=A.MODEL_ID AND K.GROUP_CODE IN("+buffer+"))\n");
			}
//			sql.append(" AND EXISTS (SELECT K.GROUP_ID FROM TM_VHCL_MATERIAL_GROUP K WHERE K.GROUP_ID=A.MODEL_ID AND K.GROUP_CODE LIKE '%"+model+"%') \n");
		}
		if (Utility.testString(reportStartDate)) {//提报开始日期
			sql.append("   AND A.CREATE_DATE >= TO_DATE('").append(reportStartDate.trim().concat(" 00:00:00")).append("','YYYY-MM-DD HH24:MI:SS') \n");
		}
		if (Utility.testString(reportEndDate)) {//提报结束日期
			sql.append("   AND A.CREATE_DATE <= TO_DATE('").append(reportEndDate.trim().concat(" 23:59:59")).append("','YYYY-MM-DD HH24:MI:SS') \n");
		}
		if (Utility.testString(saleStartDate)) {//提报开始日期
			sql.append("   AND A.SALES_DATE >= TO_DATE('").append(saleStartDate.trim().concat(" 00:00:00")).append("','YYYY-MM-DD HH24:MI:SS') \n");
		}
		if (Utility.testString(saleEndDate)) {//提报结束日期
			sql.append("   AND A.SALES_DATE <= TO_DATE('").append(saleEndDate.trim().concat(" 23:59:59")).append("','YYYY-MM-DD HH24:MI:SS') \n");
		}
		if(Utility.testString(orgId)){
			sql.append(" AND A.ORG_ID = "+orgId+" \n");
		}
		if(Utility.testString(areaId)){
			sql.append(" AND EXISTS (SELECT H.AREA_ID FROM TM_DEALER_BUSINESS_AREA H WHERE H.DEALER_ID=A.DEALER_ID AND H.AREA_ID LIKE '%"+areaId+"%') \n");
		}
		if(Utility.testString(energyNo)){
			sql.append(" AND A.CONSERVATION_NO LIKE '%"+energyNo+"%' \n");
		}
		if(Utility.testString(dPro)){
			sql.append(" AND A.DLR_PROVICE_ID = "+dPro+" \n");
		}
		if(Utility.testString(dCity)){
			sql.append(" AND A.DLR_CITY_ID = "+dCity+" \n");
		}
		if(Utility.testString(vin)){
			sql.append(" AND EXISTS (SELECT I.VEHICLE_ID FROM TM_VEHICLE I WHERE I.VEHICLE_ID=A.VEHICLE_ID "+GetVinUtil.getVins(vin,"I")+") \n");
		}
		if(Utility.testString(dealerCode)){
			if (null != dealerCode && !"".equals(dealerCode)) {
				String[] array = dealerCode.split(",");
				sql.append("   AND EXISTS (SELECT J.DEALER_ID FROM TM_DEALER J WHERE J.DEALER_ID=A.DEALER_ID AND J.DEALER_CODE IN (\n");
				for (int i = 0; i < array.length; i++) {
					sql.append("'" + array[i] + "'");
					if (i != array.length - 1) {
						sql.append(",");
					}
				}
				sql.append("))\n");
			}
//			sql.append(" AND EXISTS (SELECT J.DEALER_ID FROM TM_DEALER J WHERE J.DEALER_ID=A.DEALER_ID AND J.DEALER_CODE LIKE '%"+dealerCode+"%') \n");
		}
		
		PageResult<Map<String, Object>> ps = pageQuery(sql.toString(), null, getFunName(), pageSize, curPage);
		return ps;
	}
	
	public PageResult<Map<String, Object>> energyConExportQuery(
			String reportStartDate,String reportEndDate,
			String dealerCode,String model,String orgId,String areaId,
			String energyNo,String vin,String dPro,String dCity,
			int curPage, int pageSize) {		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT A.CONSERVATION_ID,\n");
		sql.append("       A.CONSERVATION_NO,\n");
		sql.append("       A.DEALER_ID, (SELECT B.DEALER_CODE\n");
		sql.append("                      FROM TM_DEALER B\n");
		sql.append("                     WHERE B.DEALER_ID = A.DEALER_ID) AS DEALERCODE,\n");
		sql.append("       (SELECT C.DEALER_NAME\n");
		sql.append("          FROM TM_DEALER C\n");
		sql.append("         WHERE C.DEALER_ID = A.DEALER_ID) AS DEALERNAME,\n");
		sql.append("       (SELECT D.GROUP_CODE\n");
		sql.append("          FROM TM_VHCL_MATERIAL_GROUP D\n");
		sql.append("         WHERE D.GROUP_ID = A.MODEL_ID) AS MODELCODE,\n");
		sql.append("       (SELECT E.GROUP_NAME\n");
		sql.append("          FROM TM_VHCL_MATERIAL_GROUP E\n");
		sql.append("         WHERE E.GROUP_ID = A.MODEL_ID) AS MODELNAME,\n");
		sql.append("       A.SALES_ID,\n");
		sql.append("       (SELECT G.VIN FROM TM_VEHICLE G WHERE G.VEHICLE_ID=A.VEHICLE_ID) AS VIN,\n");
		sql.append("       A.VEHICLE_ID,\n");
		sql.append("       A.MODEL_ID,\n");
		sql.append("       TO_CHAR(A.SALES_DATE, 'YYYY-MM-dd') AS SALES_DATE,\n");
		sql.append("       A.INVOCE_NO,\n");
		sql.append("       A.VEHICLE_NO,\n");
		sql.append("       A.FACTORY_PRICE,\n");
		sql.append("       A.SALES_PRICE,\n");
		sql.append("       A.DLR_LINKMAN,\n");
		sql.append("       A.DLR_LINKTEL,\n");
		sql.append("       A.DLR_ZIP_CODE,\n");
		sql.append("       A.DLR_PROVICE_ID,\n");
		sql.append("       A.DLR_ADDRESS,\n");
		sql.append("       (SELECT F.CTM_NAME FROM TT_CUSTOMER F WHERE F.CTM_ID=A.CTM_ID) AS CTM_NAME,\n");
		sql.append("       A.CTM_ID,\n");
		sql.append("       A.CTM_LINKMAN,\n");
		sql.append("       A.CTM_LINKTEL,\n");
		sql.append("       A.CTM_ZIP_CODE,\n");
		sql.append("       A.CTM_PROVICE_ID,\n");
		sql.append("       A.CTM_ADDRESS,\n");
		sql.append("       A.ORG_ID,\n");
		sql.append("       A.CONSERVATION_STATUS,\n");
		sql.append("       A.STATUS,\n");
		sql.append("       A.IS_EXPORT,\n");
		sql.append("       A.EXPORT_FLAG,\n");
		sql.append("       A.EXPORT_USER,\n");
		sql.append("       A.EXPORT_POSE,\n");
		sql.append("       A.CREATE_BY,\n");
		sql.append("       TO_CHAR(A.CREATE_DATE, 'YYYY-MM-dd') AS CREATE_DATE,\n");
		sql.append("       A.REMARK\n");
		sql.append("  FROM tt_vs_energy_conservation A\n");
		sql.append(" WHERE 1=1 AND A.CONSERVATION_STATUS="+Constant.VEHICLE_ENERGY_CON_SALEPASS+"\n");
		sql.append("  AND A.IS_EXPORT = 0\n");

		if(Utility.testString(model)){
			String[] materialCodes = model.split(",");
			if (null != materialCodes && materialCodes.length > 0) {
				StringBuffer buffer = new StringBuffer();
				for (int i = 0; i < materialCodes.length; i++) {
					buffer.append("'").append(materialCodes[i]).append("'").append(",");
				}
				buffer = buffer.deleteCharAt(buffer.length() - 1);
				sql.append(" AND EXISTS (SELECT K.GROUP_ID FROM TM_VHCL_MATERIAL_GROUP K WHERE K.GROUP_ID=A.MODEL_ID AND K.GROUP_CODE IN("+buffer+"))\n");
			}
//			sql.append(" AND EXISTS (SELECT K.GROUP_ID FROM TM_VHCL_MATERIAL_GROUP K WHERE K.GROUP_ID=A.MODEL_ID AND K.GROUP_CODE LIKE '%"+model+"%') \n");
		}
		if (Utility.testString(reportStartDate)) {//提报开始日期
			sql.append("   AND A.CREATE_DATE >= TO_DATE('").append(reportStartDate.trim().concat(" 00:00:00")).append("','YYYY-MM-DD HH24:MI:SS') \n");
		}
		if (Utility.testString(reportEndDate)) {//提报结束日期
			sql.append("   AND A.CREATE_DATE <= TO_DATE('").append(reportEndDate.trim().concat(" 23:59:59")).append("','YYYY-MM-DD HH24:MI:SS') \n");
		}
		if(Utility.testString(orgId)){
			sql.append(" AND A.ORG_ID = "+orgId+" \n");
		}
		if(Utility.testString(areaId)){
			sql.append(" AND EXISTS (SELECT H.AREA_ID FROM TM_DEALER_BUSINESS_AREA H WHERE H.DEALER_ID=A.DEALER_ID AND H.AREA_ID LIKE '%"+areaId+"%') \n");
		}
		if(Utility.testString(energyNo)){
			sql.append(" AND A.CONSERVATION_NO LIKE '%"+energyNo+"%' \n");
		}
		if(Utility.testString(dPro)){
			sql.append(" AND A.DLR_PROVICE_ID = "+dPro+" \n");
		}
		if(Utility.testString(dCity)){
			sql.append(" AND A.DLR_CITY_ID = "+dCity+" \n");
		}
		if(Utility.testString(vin)){
			sql.append(" AND EXISTS (SELECT I.VEHICLE_ID FROM TM_VEHICLE I WHERE I.VEHICLE_ID=A.VEHICLE_ID "+GetVinUtil.getVins(vin,"I")+") \n");
		}
		if(Utility.testString(dealerCode)){
			if (null != dealerCode && !"".equals(dealerCode)) {
				String[] array = dealerCode.split(",");
				sql.append("   AND EXISTS (SELECT J.DEALER_ID FROM TM_DEALER J WHERE J.DEALER_ID=A.DEALER_ID AND J.DEALER_CODE IN (\n");
				for (int i = 0; i < array.length; i++) {
					sql.append("'" + array[i] + "'");
					if (i != array.length - 1) {
						sql.append(",");
					}
				}
				sql.append("))\n");
			}
//			sql.append(" AND EXISTS (SELECT J.DEALER_ID FROM TM_DEALER J WHERE J.DEALER_ID=A.DEALER_ID AND J.DEALER_CODE LIKE '%"+dealerCode+"%') \n");
		}
		
		PageResult<Map<String, Object>> ps = pageQuery(sql.toString(), null, getFunName(), pageSize, curPage);
		return ps;
	}
	
	/**
	 * 节能惠民申请查询
	 * @param dealerId
	 * @param curPage
	 * @param pageSize
	 * @return
	 */
	
	public Map<String, Object> energyConDetailCheck(String conservationId) {		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT A.CONSERVATION_ID,\n");
		sql.append("       A.CONSERVATION_NO,\n");
		sql.append("       A.DEALER_ID, (SELECT B.DEALER_CODE\n");
		sql.append("                      FROM TM_DEALER B\n");
		sql.append("                     WHERE B.DEALER_ID = A.DEALER_ID) AS DEALERCODE,\n");
		sql.append("       (SELECT C.DEALER_NAME\n");
		sql.append("          FROM TM_DEALER C\n");
		sql.append("         WHERE C.DEALER_ID = A.DEALER_ID) AS DEALERNAME,\n");
		sql.append("       (SELECT D.GROUP_CODE\n");
		sql.append("          FROM TM_VHCL_MATERIAL_GROUP D\n");
		sql.append("         WHERE D.GROUP_ID = A.MODEL_ID) AS MODELCODE,\n");
		sql.append("       (SELECT E.GROUP_NAME\n");
		sql.append("          FROM TM_VHCL_MATERIAL_GROUP E\n");
		sql.append("         WHERE E.GROUP_ID = A.MODEL_ID) AS MODELNAME,\n");
		sql.append("       A.SALES_ID,\n");
		sql.append("       (SELECT G.VIN FROM TM_VEHICLE G WHERE G.VEHICLE_ID=A.VEHICLE_ID) AS VIN,\n");
		sql.append("       A.VEHICLE_ID,\n");
		sql.append("       A.MODEL_ID,\n");
		sql.append("       TO_CHAR(A.SALES_DATE, 'YYYY-MM-dd') AS SALES_DATE,\n");
		sql.append("       A.INVOCE_NO,\n");
		sql.append("       A.VEHICLE_NO,\n");
		sql.append("       A.FACTORY_PRICE,\n");
		sql.append("       A.SALES_PRICE,\n");
		sql.append("       A.DLR_LINKMAN,\n");
		sql.append("       A.DLR_LINKTEL,\n");
		sql.append("       A.DLR_ZIP_CODE,\n");
		sql.append("       (SELECT H.REGION_NAME FROM TM_REGION H WHERE H.REGION_CODE=A.DLR_PROVICE_ID) AS DLR_PROVICE_ID,\n");
		sql.append("       (SELECT H.REGION_NAME FROM TM_REGION H WHERE H.REGION_CODE=A.DLR_CITY_ID) AS DLR_CITY_ID,\n");
		sql.append("       A.DLR_ADDRESS,\n");
		sql.append("       (SELECT F.CTM_NAME FROM TT_CUSTOMER F WHERE F.CTM_ID=A.CTM_ID) AS CTM_NAME,\n");
		sql.append("       A.CTM_ID,\n");
		sql.append("       A.CTM_LINKMAN,\n");
		sql.append("       A.CTM_LINKTEL,\n");
		sql.append("       A.CTM_ZIP_CODE,\n");
		sql.append("       (SELECT H.REGION_NAME FROM TM_REGION H WHERE H.REGION_CODE=A.CTM_PROVICE_ID) AS CTM_PROVICE_ID,\n");
		sql.append("       (SELECT H.REGION_NAME FROM TM_REGION H WHERE H.REGION_CODE=A.CTM_CITY_ID) AS CTM_CITY_ID,\n");
		sql.append("       (SELECT H.REGION_NAME FROM TM_REGION H WHERE H.REGION_CODE=A.CTM_TOWN_ID) AS CTM_TOWN_ID,\n");
		sql.append("       A.CTM_ADDRESS,\n");
		sql.append("       A.ORG_ID,\n");
		sql.append("       A.CONSERVATION_STATUS,\n");
		sql.append("       A.STATUS,\n");
		sql.append("       A.IS_EXPORT,\n");
		sql.append("       A.EXPORT_FLAG,\n");
		sql.append("       A.EXPORT_USER,\n");
		sql.append("       A.EXPORT_POSE,\n");
		sql.append("       A.CREATE_BY,\n");
		sql.append("       TO_CHAR(A.CREATE_DATE, 'YYYY-MM-dd') AS CREATE_DATE,\n");
		sql.append("       A.REMARK\n");
		sql.append("  FROM tt_vs_energy_conservation A\n");
		sql.append(" WHERE A.CONSERVATION_ID="+conservationId+"\n");


		Map<String, Object> ps = pageQueryMap(sql.toString(), null, getFunName());
		return ps;
	}
	
	/**
	 * 节能惠民申请查询
	 * @param dealerId
	 * @param curPage
	 * @param pageSize
	 * @return
	 */
	
	public List<Map<String, Object>> energyConStatusInfo(String conservationId) {		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT A.CHANGE_ID, A.STATUS, A.DESCRIPTION, TO_CHAR(A.CREATE_DATE, 'YYYY-MM-dd') AS CREATE_DATE, (SELECT B.NAME FROM TC_USER B WHERE B.USER_ID=A.CREATE_BY) AS CREATE_BY\n");
		sql.append("  FROM tt_vs_energy_conservation_chg A\n");
		sql.append(" WHERE A.CONSERVATION_ID = "+conservationId+"\n");

		List<Map<String, Object>> ps = pageQuery(sql.toString(), null, getFunName());
		return ps;
	}
	
	/**
	 * 节能惠民申请查询
	 * @param dealerId
	 * @param curPage
	 * @param pageSize
	 * @return
	 */
	
	public PageResult<Map<String, Object>> energyConOutCheckQuery(
			String expVer,String saleStartDate,
			String saleEndDate,String reportStartDate,String reportEndDate,
			String dealerCode,String model,String orgId,String areaId,
			String energyNo,String vin,String dPro,String dCity,
			int curPage, int pageSize) {		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT A.CONSERVATION_ID,\n");
		sql.append("       A.CONSERVATION_NO,\n");
		sql.append("       A.DEALER_ID, (SELECT B.DEALER_CODE\n");
		sql.append("                      FROM TM_DEALER B\n");
		sql.append("                     WHERE B.DEALER_ID = A.DEALER_ID) AS DEALERCODE,\n");
		sql.append("       (SELECT C.DEALER_NAME\n");
		sql.append("          FROM TM_DEALER C\n");
		sql.append("         WHERE C.DEALER_ID = A.DEALER_ID) AS DEALERNAME,\n");
		sql.append("       (SELECT D.GROUP_CODE\n");
		sql.append("          FROM TM_VHCL_MATERIAL_GROUP D\n");
		sql.append("         WHERE D.GROUP_ID = A.MODEL_ID) AS MODELCODE,\n");
		sql.append("       (SELECT E.GROUP_NAME\n");
		sql.append("          FROM TM_VHCL_MATERIAL_GROUP E\n");
		sql.append("         WHERE E.GROUP_ID = A.MODEL_ID) AS MODELNAME,\n");
		sql.append("       A.SALES_ID,\n");
		sql.append("       (SELECT G.VIN FROM TM_VEHICLE G WHERE G.VEHICLE_ID=A.VEHICLE_ID) AS VIN,\n");
		sql.append("       A.VEHICLE_ID,\n");
		sql.append("       A.MODEL_ID,\n");
		sql.append("       TO_CHAR(A.SALES_DATE, 'YYYY-MM-dd') AS SALES_DATE,\n");
		sql.append("       A.INVOCE_NO,\n");
		sql.append("       A.VEHICLE_NO,\n");
		sql.append("       A.FACTORY_PRICE,\n");
		sql.append("       A.SALES_PRICE,\n");
		sql.append("       A.DLR_LINKMAN,\n");
		sql.append("       A.DLR_LINKTEL,\n");
		sql.append("       A.DLR_ZIP_CODE,\n");
		sql.append("       A.DLR_PROVICE_ID,\n");
		sql.append("       A.DLR_ADDRESS,\n");
		sql.append("       (SELECT F.CTM_NAME FROM TT_CUSTOMER F WHERE F.CTM_ID=A.CTM_ID) AS CTM_NAME,\n");
		sql.append("       A.CTM_ID,\n");
		sql.append("       A.CTM_LINKMAN,\n");
		sql.append("       A.CTM_LINKTEL,\n");
		sql.append("       A.CTM_ZIP_CODE,\n");
		sql.append("       A.CTM_PROVICE_ID,\n");
		sql.append("       A.CTM_ADDRESS,\n");
		sql.append("       A.ORG_ID,\n");
		sql.append("       A.CONSERVATION_STATUS,\n");
		sql.append("       A.STATUS,\n");
		sql.append("       A.IS_EXPORT,\n");
		sql.append("       A.EXPORT_FLAG,\n");
		sql.append("       A.EXPORT_USER,\n");
		sql.append("       A.EXPORT_POSE,\n");
		sql.append("       A.CREATE_BY,\n");
		sql.append("       TO_CHAR(A.CREATE_DATE, 'YYYY-MM-dd') AS CREATE_DATE,\n");
		sql.append("       A.REMARK\n");
		sql.append("  FROM tt_vs_energy_conservation A\n");
		sql.append(" WHERE 1=1 AND A.CONSERVATION_STATUS="+Constant.VEHICLE_ENERGY_CON_SALEPASS+"\n");
		sql.append("  AND A.IS_EXPORT=1\n");
		
		if(Utility.testString(expVer)){
			sql.append(" AND A.EXPORT_FLAG="+expVer+" \n");
		}
		if(Utility.testString(model)){
			String[] materialCodes = model.split(",");
			if (null != materialCodes && materialCodes.length > 0) {
				StringBuffer buffer = new StringBuffer();
				for (int i = 0; i < materialCodes.length; i++) {
					buffer.append("'").append(materialCodes[i]).append("'").append(",");
				}
				buffer = buffer.deleteCharAt(buffer.length() - 1);
				sql.append(" AND EXISTS (SELECT K.GROUP_ID FROM TM_VHCL_MATERIAL_GROUP K WHERE K.GROUP_ID=A.MODEL_ID AND K.GROUP_CODE IN("+buffer+"))\n");
			}
//			sql.append(" AND EXISTS (SELECT K.GROUP_ID FROM TM_VHCL_MATERIAL_GROUP K WHERE K.GROUP_ID=A.MODEL_ID AND K.GROUP_CODE LIKE '%"+model+"%') \n");
		}
		if (Utility.testString(reportStartDate)) {//提报开始日期
			sql.append("   AND A.CREATE_DATE >= TO_DATE('").append(reportStartDate.trim().concat(" 00:00:00")).append("','YYYY-MM-DD HH24:MI:SS') \n");
		}
		if (Utility.testString(reportEndDate)) {//提报结束日期
			sql.append("   AND A.CREATE_DATE <= TO_DATE('").append(reportEndDate.trim().concat(" 23:59:59")).append("','YYYY-MM-DD HH24:MI:SS') \n");
		}
		if (Utility.testString(saleStartDate)) {//提报开始日期
			sql.append("   AND A.SALES_DATE >= TO_DATE('").append(saleStartDate.trim().concat(" 00:00:00")).append("','YYYY-MM-DD HH24:MI:SS') \n");
		}
		if (Utility.testString(saleEndDate)) {//提报结束日期
			sql.append("   AND A.SALES_DATE <= TO_DATE('").append(saleEndDate.trim().concat(" 23:59:59")).append("','YYYY-MM-DD HH24:MI:SS') \n");
		}
		if(Utility.testString(orgId)){
			sql.append(" AND A.ORG_ID = "+orgId+" \n");
		}
		if(Utility.testString(dPro)){
			sql.append(" AND A.DLR_PROVICE_ID = "+dPro+" \n");
		}
		if(Utility.testString(dCity)){
			sql.append(" AND A.DLR_CITY_ID = "+dCity+" \n");
		}
		if(Utility.testString(areaId)){
			sql.append(" AND EXISTS (SELECT H.AREA_ID FROM TM_DEALER_BUSINESS_AREA H WHERE H.DEALER_ID=A.DEALER_ID AND H.AREA_ID LIKE '%"+areaId+"%') \n");
		}
		if(Utility.testString(energyNo)){
			sql.append(" AND A.CONSERVATION_NO LIKE '%"+energyNo+"%' \n");
		}
		if(Utility.testString(vin)){
			sql.append(" AND EXISTS (SELECT I.VEHICLE_ID FROM TM_VEHICLE I WHERE I.VEHICLE_ID=A.VEHICLE_ID "+GetVinUtil.getVins(vin,"I")+") \n");
		}
		if(Utility.testString(dealerCode)){
			if (null != dealerCode && !"".equals(dealerCode)) {
				String[] array = dealerCode.split(",");
				sql.append("   AND EXISTS (SELECT J.DEALER_ID FROM TM_DEALER J WHERE J.DEALER_ID=A.DEALER_ID AND J.DEALER_CODE IN (\n");
				for (int i = 0; i < array.length; i++) {
					sql.append("'" + array[i] + "'");
					if (i != array.length - 1) {
						sql.append(",");
					}
				}
				sql.append("))\n");
			}
//			sql.append(" AND EXISTS (SELECT J.DEALER_ID FROM TM_DEALER J WHERE J.DEALER_ID=A.DEALER_ID AND J.DEALER_CODE LIKE '%"+dealerCode+"%') \n");
		}
		
		PageResult<Map<String, Object>> ps = pageQuery(sql.toString(), null, getFunName(), pageSize, curPage);
		return ps;
	}
	
	/**
	 * 节能惠民申请查询
	 * @param dealerId
	 * @param curPage
	 * @param pageSize
	 * @return
	 */
	
	public List<Map<String, Object>> energyConExportVerNoQuery(String userId) {		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT DISTINCT A.EXPORT_FLAG FROM TT_VS_ENERGY_CONSERVATION A WHERE A.EXPORT_USER="+userId+" AND A.CONSERVATION_STATUS="+Constant.VEHICLE_ENERGY_CON_SALEPASS+"\n");

		
		List<Map<String, Object>> list = pageQuery(sql.toString(), null, getFunName());
		return list;
	}
	
	/**
	 * 节能惠民申请查询
	 * @param dealerId
	 * @param curPage
	 * @param pageSize
	 * @return
	 */
	
	public PageResult<Map<String, Object>> energyConViewQuery(
			String saleStartDate, String saleEndDate,String orgId,
			String dPro, String dCity, String dealerCode,
			String reportStartDate,String reportEndDate,
		    String model,String areaId,String status,
			String energyNo,String vin,String postId,
			int curPage, int pageSize) {		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT A.CONSERVATION_ID,\n");
		sql.append("       A.CONSERVATION_NO,\n");
		sql.append("       A.DEALER_ID, (SELECT B.DEALER_CODE\n");
		sql.append("                      FROM TM_DEALER B\n");
		sql.append("                     WHERE B.DEALER_ID = A.DEALER_ID) AS DEALERCODE,\n");
		sql.append("       (SELECT C.DEALER_NAME\n");
		sql.append("          FROM TM_DEALER C\n");
		sql.append("         WHERE C.DEALER_ID = A.DEALER_ID) AS DEALERNAME,\n");
		sql.append("       (SELECT D.GROUP_CODE\n");
		sql.append("          FROM TM_VHCL_MATERIAL_GROUP D\n");
		sql.append("         WHERE D.GROUP_ID = A.MODEL_ID) AS MODELCODE,\n");
		sql.append("       (SELECT E.GROUP_NAME\n");
		sql.append("          FROM TM_VHCL_MATERIAL_GROUP E\n");
		sql.append("         WHERE E.GROUP_ID = A.MODEL_ID) AS MODELNAME,\n");
		sql.append("       A.SALES_ID,\n");
		sql.append("       (SELECT G.VIN FROM TM_VEHICLE G WHERE G.VEHICLE_ID=A.VEHICLE_ID) AS VIN,\n");
		sql.append("       A.VEHICLE_ID,\n");
		sql.append("       A.MODEL_ID,\n");
		sql.append("       TO_CHAR(A.SALES_DATE, 'YYYY-MM-dd') AS SALES_DATE,\n");
		sql.append("       A.INVOCE_NO,\n");
		sql.append("       A.VEHICLE_NO,\n");
		sql.append("       A.FACTORY_PRICE,\n");
		sql.append("       A.SALES_PRICE,\n");
		sql.append("       A.DLR_LINKMAN,\n");
		sql.append("       A.DLR_LINKTEL,\n");
		sql.append("       A.DLR_ZIP_CODE,\n");
		sql.append("       A.DLR_PROVICE_ID,\n");
		sql.append("       A.DLR_ADDRESS,\n");
		sql.append("       (SELECT F.CTM_NAME FROM TT_CUSTOMER F WHERE F.CTM_ID=A.CTM_ID) AS CTM_NAME,\n");
		sql.append("       A.CTM_ID,\n");
		sql.append("       A.CTM_LINKMAN,\n");
		sql.append("       A.CTM_LINKTEL,\n");
		sql.append("       A.CTM_ZIP_CODE,\n");
		sql.append("       A.CTM_PROVICE_ID,\n");
		sql.append("       A.CTM_ADDRESS,\n");
		sql.append("       A.ORG_ID,\n");
		sql.append("       A.CONSERVATION_STATUS,\n");
		sql.append("       A.STATUS,\n");
		sql.append("       A.IS_EXPORT,\n");
		sql.append("       A.EXPORT_FLAG,\n");
		sql.append("       A.EXPORT_USER,\n");
		sql.append("       A.EXPORT_POSE,\n");
		sql.append("       A.CREATE_BY,\n");
		sql.append("       TO_CHAR(A.CREATE_DATE, 'YYYY-MM-dd') AS CREATE_DATE,\n");
		sql.append("       A.REMARK\n");
		sql.append("  FROM tt_vs_energy_conservation A\n");
		sql.append(" WHERE 1=1 \n");

		if(Utility.testString(model)){
			String[] materialCodes = model.split(",");
			if (null != materialCodes && materialCodes.length > 0) {
				StringBuffer buffer = new StringBuffer();
				for (int i = 0; i < materialCodes.length; i++) {
					buffer.append("'").append(materialCodes[i]).append("'").append(",");
				}
				buffer = buffer.deleteCharAt(buffer.length() - 1);
				sql.append(" AND EXISTS (SELECT K.GROUP_ID FROM TM_VHCL_MATERIAL_GROUP K WHERE K.GROUP_ID=A.MODEL_ID AND K.GROUP_CODE IN("+buffer+"))\n");
			}
//			sql.append(" AND EXISTS (SELECT K.GROUP_ID FROM TM_VHCL_MATERIAL_GROUP K WHERE K.GROUP_ID=A.MODEL_ID AND K.GROUP_CODE LIKE '%"+model+"%') \n");
		}
		if (Utility.testString(reportStartDate)) {//提报开始日期
			sql.append("   AND A.CREATE_DATE >= TO_DATE('").append(reportStartDate.trim().concat(" 00:00:00")).append("','YYYY-MM-DD HH24:MI:SS') \n");
		}
		if (Utility.testString(reportEndDate)) {//提报结束日期
			sql.append("   AND A.CREATE_DATE <= TO_DATE('").append(reportEndDate.trim().concat(" 23:59:59")).append("','YYYY-MM-DD HH24:MI:SS') \n");
		}
		if (Utility.testString(saleStartDate)) {//提报开始日期
			sql.append("   AND A.SALES_DATE >= TO_DATE('").append(saleStartDate.trim().concat(" 00:00:00")).append("','YYYY-MM-DD HH24:MI:SS') \n");
		}
		if (Utility.testString(saleEndDate)) {//提报结束日期
			sql.append("   AND A.SALES_DATE <= TO_DATE('").append(saleEndDate.trim().concat(" 23:59:59")).append("','YYYY-MM-DD HH24:MI:SS') \n");
		}
		if(orgId.equals(Constant.DUTY_TYPE_LARGEREGION)){
			sql.append(" AND A.ORG_ID = "+orgId+" \n");
		}
		if(Utility.testString(areaId)){
			sql.append(" AND EXISTS (SELECT H.AREA_ID FROM TM_DEALER_BUSINESS_AREA H WHERE H.DEALER_ID=A.DEALER_ID AND H.AREA_ID LIKE '%"+areaId+"%') \n");
		}
		if(Utility.testString(energyNo)){
			sql.append(" AND A.CONSERVATION_NO LIKE '%"+energyNo+"%' \n");
		}
		if(Utility.testString(status)){
			sql.append(" AND A.CONSERVATION_STATUS = "+status+" \n");
		}
		if(Utility.testString(dPro)){
			sql.append(" AND A.DLR_PROVICE_ID = "+dPro+" \n");
		}
		if(Utility.testString(dCity)){
			sql.append(" AND A.DLR_CITY_ID = "+dCity+" \n");
		}
		if(Utility.testString(vin)){
			sql.append(" AND EXISTS (SELECT I.VEHICLE_ID FROM TM_VEHICLE I WHERE I.VEHICLE_ID=A.VEHICLE_ID "+GetVinUtil.getVins(vin,"I")+") \n");
		}
		if(Utility.testString(dealerCode)){
			if (null != dealerCode && !"".equals(dealerCode)) {
				String[] array = dealerCode.split(",");
				sql.append("   AND EXISTS (SELECT J.DEALER_ID FROM TM_DEALER J WHERE J.DEALER_ID=A.DEALER_ID AND J.DEALER_CODE IN (\n");
				for (int i = 0; i < array.length; i++) {
					sql.append("'" + array[i] + "'");
					if (i != array.length - 1) {
						sql.append(",");
					}
				}
				sql.append("))\n");
			}
//			sql.append(" AND EXISTS (SELECT J.DEALER_ID FROM TM_DEALER J WHERE J.DEALER_ID=A.DEALER_ID AND J.DEALER_CODE LIKE '%"+dealerCode+"%') \n");
		}
		
		PageResult<Map<String, Object>> ps = pageQuery(sql.toString(), null, getFunName(), pageSize, curPage);
		return ps;
	}
	
	@Override
	protected PO wrapperPO(ResultSet rs, int idx) {
		// TODO Auto-generated method stub
		return null;
	}
}
